package com.pe.pgn.clubpgn.dao.impl;


import java.util.List;


import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.dao.WizardPremioCatalogoDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClprPremioCatalogo;
import com.pe.pgn.clubpgn.domain.beans.BNPremioCatalogo;

@Repository("wizardPremioCatalogoDao")
public class WizardPremioCatalogoDaoImpl extends GenericDaoHibernate<ClprPremioCatalogo, Long> implements WizardPremioCatalogoDao{

	
	JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		}
	
	private WizardPremioCatalogoDaoImpl() {
		super(ClprPremioCatalogo.class);
	}
	
	@SuppressWarnings("unchecked")
	public List<ClprPremioCatalogo> buscarPremios(String deArticulo,
			String coCodigoPremio, Long coCategoria,Long coCatalogo,
			Long coAlmacen) {
		StringBuffer query = new StringBuffer();
		
		query.append("select DISTINCT pc.id as id, ");
		query.append(" 	pc.va_canjes_limite_espejo as vaCanjesLimiteEspejo,");
		query.append("	pc.va_canjes_limite as vaCanjesLimite, ");
		query.append("	articulo.de_Articulo as deArticulo , ");
		query.append("	articulo.co_codigo_articulo as coArticulo, ");
		query.append("	pc.co_codigo_premio as coPremio, ");
		query.append("	categoria.de_Categoria as deCategoria, ");
		query.append("	unidad.de_unidad_medida as deUnidadMedida, ");
		query.append("	articulo.va_stock_actual as stockActual, ");
		query.append("	sac.va_cantidad as stockDisponible,");
		query.append(" 	articulo.st_maneja_stock_punto_canje	as stManejaStockPuntoCanje, ");
		query.append(" 	articulo.st_maneja_stock_alm_principal	as stManejaStockAlmPrincipal, ");
		query.append(" 	articulo.st_instantaneo_normal         as stInstantaneoNormal, ");
		query.append(" 	pc.va_stock_catalogo as vaStockCatalogo ");
		query.append("from clpr_premio_catalogo pc   " ) ;
		query.append("join clpd_articulo articulo 		on pc.co_premio = articulo.id " ) ;
		query.append("join clpd_unidad_medida unidad 		on articulo.co_unidad_medida = unidad.id " ) ; 
		query.append("join clpb_categoria categoria 		on articulo.co_categoria = categoria.id  " ) ;
		query.append("join clpm_catalogo catalogo 		on pc.co_catalogo = catalogo.id " ) ;
		query.append("join clpm_stock_almacen_catalogo sac 		on articulo.id = sac.co_articulo " ) ;  		
		query.append("where 1 = 1   " ) ; 		
		
		
		if (StringUtils.isNotBlank(deArticulo)) {
			query.append("		and upper(trim(articulo.de_articulo)) like '%").append(
					deArticulo.trim().toUpperCase()).append("%'");
		}
				
		if (StringUtils.isNotBlank(coCodigoPremio)) {
			query.append("		and upper(trim(pc.co_codigo_premio)) like '%").append(
					coCodigoPremio.trim().toUpperCase()).append("%'");
		}
		
		if(coCategoria.intValue() > 0){
			query.append("		and articulo.co_categoria = ").append(coCategoria);
		}
		if(coAlmacen.intValue() > 0){
			query.append("		and sac.co_almacen = ").append(coAlmacen);
		}		
		
		query.append("	and pc.co_catalogo = ").append(coCatalogo);
		query.append("	and sac.co_catalogo is null   " ) ;
		query.append("	and ( articulo.st_maneja_stock_punto_canje = 'Y'  " ) ;		
		query.append("	or articulo.ST_MANEJA_STOCK_ALM_PRINCIPAL = 'Y' or articulo.st_instantaneo_normal='Y')  " ) ;
		
		System.out.println("*** WizardPremioCatalogoDaoImpl.buscarPremios()  modifi--> "+query.toString());
		return findListOfBeans(query.toString(), null, BNPremioCatalogo.class);
}		
	public ClprPremioCatalogo obtenerPremio(Long id) {
				ClprPremioCatalogo premio = get(id);
				return premio;
	}

	
	@SuppressWarnings("unchecked")
	public BNPremioCatalogo recuperarPremio(String coCodigoPremio,
			Long coCatalogo, Long coAlmacen) {
		// recupera premio por el coCodigoPremio verificando la existencia en el catalogo y almacen
		StringBuffer query = new StringBuffer();
		
		query.append("select pc.id as id, ");
		query.append(" 	pc.va_canjes_limite_espejo as vaCanjesLimiteEspejo,");
		query.append("	pc.va_canjes_limite as vaCanjesLimite, ");
		query.append("	articulo.de_Articulo as deArticulo , ");
		query.append("	articulo.co_codigo_articulo as coArticulo, ");
		query.append("	pc.co_codigo_premio as coPremio, ");
		query.append("	categoria.de_Categoria as deCategoria, ");
		query.append("	unidad.de_unidad_medida as deUnidadMedida, ");
		query.append("	articulo.va_stock_actual as stockActual, ");
		query.append("	sac.va_cantidad as stockDisponible,");
		query.append(" 	articulo.st_maneja_stock_punto_canje	as stManejaStockPuntoCanje, ");
		query.append(" 	articulo.st_maneja_stock_alm_principal	as stManejaStockAlmPrincipal, ");
		query.append(" 	articulo.st_instantaneo_normal         as stInstantaneoNormal, ");
		query.append(" 	pc.va_stock_catalogo as vaStockCatalogo ");
		
		query.append("from clpr_premio_catalogo pc   " ) ;
		query.append("join clpd_articulo articulo 		on pc.co_premio = articulo.id " ) ;
		query.append("join clpd_unidad_medida unidad 		on articulo.co_unidad_medida = unidad.id " ) ; 
		query.append("join clpb_categoria categoria 		on articulo.co_categoria = categoria.id  " ) ;
		query.append("join clpm_catalogo catalogo 		on pc.co_catalogo = catalogo.id " ) ;
		query.append("join clpm_stock_almacen_catalogo sac 		on articulo.id = sac.co_articulo " ) ;  		
		query.append("where  1 = 1" ) ;	
		
		query.append(" and upper(trim(pc.co_codigo_premio)) = '" );
		query.append(coCodigoPremio.trim().toUpperCase()).append("'");		
		
		if(coAlmacen.intValue() > 0){
			query.append("	and sac.co_almacen = ").append(coAlmacen);
		}
		
		query.append("	and pc.co_catalogo = ").append(coCatalogo);		
		query.append("	and sac.co_catalogo is null   " );		
		query.append("	and ( articulo.st_maneja_stock_punto_canje = 'Y'  " ) ;		
		query.append("	or articulo.st_maneja_stock_alm_principal = 'Y' or articulo.st_instantaneo_normal='Y')  " ) ;		
		
		System.out.println("*** WizardPremioCatalogoDaoImpl.recuperarPremio()  --> "+query.toString());		
		List<BNPremioCatalogo> list = findListOfBeans(query.toString(), null, BNPremioCatalogo.class);
		
		if(!list.isEmpty()){
			return (BNPremioCatalogo)list.get(0);
		}else{
			return new BNPremioCatalogo();
		}
	}

}
